package root.dao.impl;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

import root.dao.LybDao;
import root.entity.vo.LiuyanVO;
import root.entity.vo.PageInfo;

public class LybDaoimpl implements LybDao {
	static {
		try {
			Class.forName("oracle.jdbc.OracleDriver");
		} catch (Exception e) {
			// TODO: handle exception
		}
	}
	@Override
	public void flushData(String name, String value, String target, String type, PageInfo page) {
			try {
				String url,user,password;
				url= "jdbc:oracle:thin:@120.77.168.195:1521:orcl";
				user = "demo";
				password = "demo";
				Connection conn = DriverManager.getConnection(url, user, password);
				
//				根据传递的参数动态变化sql 语句
//				比如传递了  name 和value
//				sql 语句   就应该时  select * from tablename where del ='false' and name = ? and value = ?
//				如果传递了  type 和page
//				sql 语句   就应该时  select * from tablename where del ='false' and type = ? and page = ?
				String sql = "select * from liuyan where del = 'false'";
				if(name!=null&&name.equals("")) sql+=" and name = ?";
				if(value!=null&&value.equals("")) sql+=" and value = ?";
				if(target!=null&&target.equals("")) sql+=" and target = ?";
				if(type!=null&&type.equals("")) sql+=" and type = ?";
				
//				因为是Oracle 数据库的hot 要加入 主键排序
//				oracle  支持两种   hot（默认）   iot
//				mysql   支持一种   iot
				sql +=" order by id desc";
//				因为是Oracle 数据库 ，使用 12c 的分页语法
				sql +=" offset "+(page.getFenyepagesize()*(page.getFenyepageindex()-1))
					+ " rows fetch next "+page.getFenyepagesize()+" rows only";
//				select * from  tablename 
//				where del ='false' and name = ? and value = ? 
//				order by id desc 
//				offset 10 rows
//				fetch next 10 rows only
				System.out.println(sql);
				PreparedStatement pstm = conn.prepareStatement(sql);
				int count = 1;
				if(name!=null&&name.equals("")) pstm.setString(count++, name);
				if(value!=null&&value.equals("")) pstm.setString(count++, value);
				if(target!=null&&target.equals("")) pstm.setString(count++, target);
				if(type!=null&&type.equals("")) pstm.setString(count++, type);
				
				ResultSet rs = pstm.executeQuery();
				List list = new ArrayList(page.getFenyepagesize());
				page.setList(list);
				while(rs.next()) {
					LiuyanVO vo = new LiuyanVO();
					vo.setId((Integer) rs.getObject("id"));
					vo.setName(rs.getString("name"));
					vo.setTarget(rs.getString("target"));
					vo.setValue(rs.getString("value"));
					vo.setType(rs.getString("type"));
					vo.setDatetime(new Date(rs.getTimestamp("datetime").getTime()));
					list.add(vo);
				}
				rs.close();
				pstm.close();
				conn.close();
			} catch (Exception e) {
				
			}
	}

}
